package cn.com.callback

import groovy.json.*;
import groovy.swing.impl.DefaultAction;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.RequestDispatcher;
import cn.com.elite.EliteBean;
import java.security.MessageDigest;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.wisdge.dataservice.Result;
import java.util.Map;
import java.util.Date;
import java.util.HashMap;
import cn.com.elite.Util;
import cn.com.kh.UcisXML;
import java.net.URLEncoder;

import org.json.JSONObject;
import org.json.JSONArray;
import org.json.JSONException;

/**
 *
 * @Title: BigdateWorkMonth
 * @Description:大数据定时更新每月工单数据
 * @author: Yuchieh 
 * @date 2021-07-13
 *
 */


public class BigdateWorkMonth implements ServiceBackInvoke{
	private static final Log log=LogFactory.getLog(BigdateWorkMonth.class);
	public String doCallBack(EliteBean eliteBean,HttpServletRequest request,HttpServletResponse response){
				
		response.setCharacterEncoding("UTF-8");
		JSONObject resulterror = new JSONObject();
				

		try{
			//将数据写进his表
			def todaysql_bak = "INSERT INTO bigdateWork_month_his SELECT *,GETDATE() FROM bigdateWork_month";
			//eliteBean.update(todaysql_bak, "dataSource");
			//清空表数据
			def todaysql_del = "TRUNCATE TABLE bigdateWork_month";
			eliteBean.update(todaysql_del, "dataSource");
			//写入数据
			def todaysql_ins1 = "INSERT INTO bigdateWork_month SELECT T1.[区域],报修,投诉,业务,其它,咨询,isnull(当月登记工单,0)'当月登记工单',isnull(当月已处理完成工单数,0)'当月已处理完成工单数',isnull(当月回访工单数,0)'当月回访工单数',isnull(当月未处理工单数,0)'当月未处理工单数',isnull(当月催办工单,0)'当月催办工单',isnull(当月不满意工单数,0)'当月不满意工单数',isnull(当月超时处理工单数,0)'当月超时处理工单数' FROM ( SELECT ga.Area_desc '区域', SUM(CASE WHEN c.Code_name = '报修' THEN 1 ELSE 0 END)'报修', SUM(CASE WHEN c.Code_name = '投诉' THEN 1 ELSE 0 END)'投诉', SUM(CASE WHEN c.Code_name = '业务' THEN 1 ELSE 0 END)'业务', SUM(CASE WHEN c.Code_name = '其它' THEN 1 ELSE 0 END)'其它',SUM(CASE WHEN c.Code_name = '咨询' THEN 1 ELSE 0 END)'咨询' FROM TABCswgdmerge t1 LEFT JOIN C_CodeTables c on c.Code_id = t1.registerbusiness1 AND use_type = '一级业务类型' LEFT JOIN Group_Area ga on ga.area_id = t1.receiver where 1=1 AND registerdate > (convert(varchar(7),getdate(),120) + '-01')  AND registerdate < convert(varchar(10),getdate(),120) GROUP BY ga.Area_desc )T1 LEFT JOIN ( SELECT top 100 ga.Area_desc '区域',COUNT(*) '当月登记工单' FROM TABCswgdmerge t1  LEFT JOIN C_CodeTables c on c.Code_id = t1.registerbusiness1 AND use_type = '一级业务类型' LEFT JOIN Group_Area ga on ga.area_id = t1.receiver where 1=1 AND registerdate > (convert(varchar(7),getdate(),120) + '-01') AND registerdate < convert(varchar(10),getdate(),120) GROUP BY ga.Area_desc )T2 ON T1.[区域]=T2.[区域] LEFT JOIN ( SELECT ga.Area_desc 区域,isnull(COUNT(*),0) 当月已处理完成工单数 FROM TABCswgdmerge t1 LEFT JOIN Group_Area ga on ga.area_id = t1.receiver where 1=1 AND registerdate > (convert(varchar(7),getdate(),120) + '-01') AND registerdate < convert(varchar(10),getdate(),120) AND t1.handledescription !='' GROUP BY ga.Area_desc )T3 ON T1.[区域] = T3.[区域] LEFT JOIN ( SELECT ga.Area_desc 区域,isnull(COUNT(*),0) 当月回访工单数 FROM TABCswgdmerge t1  LEFT JOIN Group_Area ga on ga.area_id = t1.receiver where 1=1 AND registerdate > (convert(varchar(7),getdate(),120) + '-01') AND registerdate < convert(varchar(10),getdate(),120) AND t1.workorderstatus = '5'  GROUP BY ga.Area_desc )T4 on T1.[区域] = T4.[区域] LEFT JOIN ( SELECT ga.Area_desc 区域,COUNT(*) 当月未处理工单数 FROM objective o LEFT JOIN TABCswgdmerge t1 on o.objective_guid=t1.relation_id LEFT JOIN Group_Area ga on ga.area_id = t1.receiver where 1=1 AND registerdate > (convert(varchar(7),getdate(),120) + '-01') AND registerdate < convert(varchar(10),getdate(),120) AND t1.handledescription = '' GROUP BY ga.Area_desc )T5 ON T1.[区域] = T5.[区域] LEFT JOIN ( SELECT ga.Area_desc 区域,COUNT(*) 当月催办工单 FROM TABCswgdmerge t1 LEFT JOIN Group_Area ga on ga.area_id = t1.receiver where 1=1 AND registerdate > (convert(varchar(7),getdate(),120) + '-01') AND registerdate < convert(varchar(10),getdate(),120) AND t1.workorderstatus = '2' GROUP BY ga.Area_desc )T6 ON T1.[区域] = T6.[区域] LEFT JOIN ( SELECT top 100 ga.Area_desc '区域',COUNT(*) '当月不满意工单数' FROM TABCswgdmerge t1  LEFT JOIN C_CodeTables c on  c.Code_id = t1.registerbusiness1 AND use_type = '一级业务类型' LEFT JOIN Group_Area ga on ga.area_id = t1.receiver where 1=1 AND registerdate > (convert(varchar(7),getdate(),120) + '-01') AND registerdate < convert(varchar(10),getdate(),120) AND t1.satisfaction = '3' GROUP BY ga.Area_desc )T7 on T1.[区域] = T7.[区域] LEFT JOIN ( SELECT  ga.Area_desc 区域,COUNT(*)当月超时处理工单数 FROM objective o LEFT JOIN TABCswgdmerge t1 on o.objective_guid=t1.relation_id LEFT JOIN Group_Area ga on ga.area_id = t1.receiver where 1=1 AND registerdate > (convert(varchar(7),getdate(),120) + '-01') AND registerdate <  convert(varchar(10),getdate(),120)   and o.objectivestatus='OPEN' and datediff(MINUTE,finishtime,t1.datelimit)<0  GROUP BY ga.Area_desc ) T8 ON T1.[区域] = T8.[区域] ORDER BY CHARINDEX(T1.[区域],'集团宝安龙岗光明') ";
			eliteBean.update(todaysql_ins1, "dataSource");
			
			resulterror.put("code", "1");
			resulterror.put("msg", "每月工单数据写入完成");
			return  resulterror;
			
		}catch(Exception e){
			resulterror.put("code", "-1");
			resulterror.put("msg", "接口异常！"+e);
			return  resulterror;
		}
	}
}